import re
from collections import defaultdict

def segment_sql_script(sql_text):
    """
    使用正向零宽断言对SQL脚本进行父子分块
    返回结构：{父块: [子块1, 子块2, ...]}
    """
    # 匹配父块（表结构定义）的正则表达式
    # 使用正向零宽断言确保匹配以 "-- Table structure for" 开头的内容
    parent_pattern = r'--+ Table structure for\s+(\w+)[\s\S]*?(?=(?:--+ Table structure for|\Z))'
    
    # 匹配子块（记录插入）的正则表达式  
    # 使用正向零宽断言确保匹配以 "-- Records of" 开头的内容 
    child_pattern = r'(?<=-- Records of\s)[\s\S]*?(?=--+ Records of|-- Table structure for|\Z)'
    
    # 查找所有父块
    parents = re.finditer(parent_pattern, sql_text, re.MULTILINE)
    
    # 创建父子结构字典
    block_structure = defaultdict(list)

    # 父块字典
    parent_blocks = {}
    
    for parent in parents:
        parent_block = parent.group(0)
        table_name = parent.group(1)
        parent_blocks[table_name] = parent_block[:5000]  # 截取前1000字符作为预览
        
        # 在当前父块内查找子块
        children = re.finditer(child_pattern, parent_block, re.MULTILINE)
        for child in children:
            child_block = child.group(0)
            block_structure[table_name].append(child_block)
    
    return (dict(block_structure), parent_blocks)

def print_block_structure(block_structure):
    """打印父子块结构"""
    for table_name, children in block_structure.items():
        print(f"\n📦 Parent Block: {table_name}")
        
        for i, child in enumerate(children, 1):
            # 截取子块预览
            preview = child.strip()[:150].replace('\n', ' ')
            print(f"   ├── 🧩 Child Block {i} ({len(child)} chars)")
            print(f"   │     Preview: {preview}...")
        
        # 显示统计信息
        record_count = 0
        if children:
            # 从第一个子块中统计记录行数
            record_count = len([line for line in children[0].split('\n') if line.strip() and not line.startswith('--')])
        print(f"   └── 📊 Contains {len(children)} child block(s) with ~{record_count} records")

# 示例用法
if __name__ == "__main__":
    # 读取SQL文件内容
    with open('C:\\Users\\zhang\\mall\\document\\sql\\mall.sql', 'r', encoding='utf-8') as f:
        sql_content = f.read()
    
    # 进行分块处理
    tuple_blocks = segment_sql_script(sql_content)
    block_structure, parent_blocks = tuple_blocks    
    # 打印统计信息
    total_tables = len(block_structure)
    total_records = sum(len(children) for children in block_structure.values())
    print(f"📋 Script Summary: {total_tables} tables with {total_records} record sections")
    
    # 打印父子块结构
    # print_block_structure(block_structure)
    
    # 保存结构到文件
    with open('block_structure.txt', 'w', encoding='utf-8') as f:
        for table, children in block_structure.items():
            f.write(f"\nTable: {table}\n")
            f.write(f" {parent_blocks[table]}\n")
            for child in children:
                # f.write(f"  - Record Section ({len(child)} chars)\n")
                f.write(f"{child}\n")